package util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import pojos.Pojo;

/**
 * <p>Este objeto se usara para formar las consultas(SELECT) a la base de datos.
 * Me base en el metodo query de SQLiteDatabase de Android y lo mejore un poco,
 * añadiendo la posibilidad de hacer JOINs.</p>
 * <p> las condicion de los JOIN deben ir en orden correlativo con las tablas,
 * ejemplo:
 *
 * </p>
 * <p>tables	joinConditions	creacion del codigo SQL</p>
 * <p>0|producto p	0|p.id=c.prod	tables[0] JOIN tables[1] ON
 * joinConditions[0]</p>
 * <p>1|categoria c	1|c.id=tprod.prod	JOIN tables[2] ON joinConditions[1] </p>
 * <p>2|tipo_producto tprod	2|tprod.id=atr.prod	JOIN tables[3] ON
 * joinConditions[2]</p>
 * <p>3|atributo atr</p>
 *
 * @author Miguel
 */
public class Query {

    /**
     * Valor usado cuando se quiere obtener un array de Productos
     */
    public static final int PRODUCTO_ARRAY = 0;
    public static final int USUARIO_ARRAY = 1;
    public static final int ATRIBUTO_ARRAY = 2;
    public static final int CATEGORIA_ARRAY = 3;
    public static final int DESCARGA_ARRAY = 4;
    public static final int MOVIMIENTO_ARRAY = 5;
    public static final int PEDIDO_ARRAY = 6;
    public static final int PRODUCTOCATEGORIA_ARRAY = 7;
    public static final int PRODUCTODETALLE_ARRAY = 8;
    public static final int TIPOATRIBUTO_ARRAY = 9;
    public static final int TIPOPRODUCTO_ARRAY = 10;
    public static final int TIPOPRODUCTOCATEGORIA_ARRAY = 11;
    public static final int TIPOPRODUCTOTIPOATRIBUTO_ARRAY = 12;
    public static final int GENERIC_LIST = 13;
    // Atributos
    private int tipoRetorno;
    private boolean distinct;
    private String[] columns;
    private String[] tables;
    private String[] joinConditions;
    private String[] joinModifiers;
    private String selection;
    private String[] selectionArgs;
    private String groupBy;
    private String having;
    private String orderBy;
    private String limit;

    /**
     * Este constructor coge todos los campos
     *
     * @param tipoRetorno indica el tipo de pojos que tiene que devolver
     * @param distinct indica si el SELECT de la consulta debe llevar un     
     * DISTINCT
     */
    public Query(int tipoRetorno, boolean distinct, String[] columns,
            String[] tables, String[] joinConditions, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy, String limit) {
        iniciarValores(tipoRetorno, distinct, columns, tables, joinConditions,
                selection, selectionArgs, groupBy, having, orderBy, limit);
    }

    /**
     * Este constructor coge todos los campos sin DISTINCT
     */
    public Query(int tipoRetorno, String[] columns, String[] tables,
            String[] joinConditions, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy, String limit) {
        iniciarValores(tipoRetorno, false, columns, tables, joinConditions,
                selection, selectionArgs, groupBy, having, orderBy, limit);
    }

    /**
     * Este constructor coge todos los campos sin DISTINCT y JOIN
     */
    public Query(int tipoRetorno, String[] columns, String[] tables,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit) {
        iniciarValores(tipoRetorno, false, columns, tables, null,
                selection, selectionArgs, groupBy, having, orderBy, limit);
    }

    /**
     * Este constructor coge todos los campos sin DISTINCT, JOIN y Conditions
     */
    public Query(int tipoRetorno, String[] columns,
            String[] tables, String selection,
            String[] selectionArgs) {
        iniciarValores(tipoRetorno, false, columns, tables, null,
                selection, selectionArgs, null, null, null, null);
    }

    public Query(int tipoRetorno, String[] columns,
            String[] tables) {
        iniciarValores(tipoRetorno, false, columns, tables, null,
                null, null, null, null, null, null);
    }

    /**
     * Metodo para inicializar los valores una sola vez
     */
    private void iniciarValores(int tipoRetorno, boolean distinct,
            String[] columns, String[] tables, String[] joinConditions,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit) {
        this.setTipoRetorno(tipoRetorno);
        this.setDistinct(distinct);
        this.setColumns(columns);
        this.setTables(tables);
        this.setJoinConditions(joinConditions);
        this.setSelection(selection);
        this.setSelectionArgs(selectionArgs);
        this.setGroupBy(groupBy);
        this.setHaving(having);
        this.setOrderBy(orderBy);
        this.setLimit(limit);
    }

    public Pojo[] getPojosFromDb(Connection con) throws SQLException, ParseException {
        Pojo[] result = null;
        switch (tipoRetorno) {
            case PRODUCTO_ARRAY:
                result = PojosFactory.getProductosFromRS(obtenerResultSet(con));
                break;
            case USUARIO_ARRAY:
                result = PojosFactory.getUsuariosFromRS(obtenerResultSet(con));
                break;
            case ATRIBUTO_ARRAY:
                result = PojosFactory.getAtributosFromRS(obtenerResultSet(con));
                break;
            case CATEGORIA_ARRAY:
                result = PojosFactory.getCategoriasFromRS(obtenerResultSet(con));
                break;
            case DESCARGA_ARRAY:
                result = PojosFactory.getDescargasFromRS(obtenerResultSet(con));
                break;
            case MOVIMIENTO_ARRAY:
                result = PojosFactory.getMovimientosFromRS(obtenerResultSet(con));
                break;
            case PEDIDO_ARRAY:
                result = PojosFactory.getPedidosFromRS(obtenerResultSet(con));
                break;
            case PRODUCTOCATEGORIA_ARRAY:
                result = PojosFactory.getProductoCategoriaFromRS(obtenerResultSet(con));
                break;
            case PRODUCTODETALLE_ARRAY:
                result = PojosFactory.getProductoDetalleFromRS(obtenerResultSet(con));
                break;
            case TIPOATRIBUTO_ARRAY:
                result = PojosFactory.getTipoAtributoFromRS(obtenerResultSet(con));
                break;
            case TIPOPRODUCTO_ARRAY:
                result = PojosFactory.getTipoProductoFromRS(obtenerResultSet(con));
                break;
            case TIPOPRODUCTOCATEGORIA_ARRAY:
                result = PojosFactory.getTipoProductoCategoriaFromRS(obtenerResultSet(con));
                break;
            case TIPOPRODUCTOTIPOATRIBUTO_ARRAY:
                result = PojosFactory.getTipoProductoTipoAtributoFromRS(obtenerResultSet(con));
                break;
            case GENERIC_LIST:
                result = PojosFactory.getGenericList(obtenerResultSet(con));
                break;
        }

        return result;
    }

    public String obtenerSQL() {
        String sql = "SELECT ";
        if (distinct) {
            sql += "DISTINCT ";
        }
        for (int i = 0; i < columns.length; i++) {
            sql += columns[i];
            if (i < columns.length - 1) {
                sql += ", ";
            } else {
                sql += " ";
            }
        }
        sql += "FROM ";
        if (joinConditions != null) {
            if (joinModifiers != null && joinModifiers.length == joinConditions.length) {
                for (int i = 0; i < tables.length; i++) {
                    if (i == 0) {
                        sql += tables[i] + " " + joinModifiers[i] + " JOIN "
                                + tables[i + 1] + " ON " + joinConditions[i] + " ";
                        i++;
                    } else {
                        sql += joinModifiers[i - 1] + " JOIN " 
                                + tables[i] + " ON " + joinConditions[i - 1] + " ";

                    }
                }
            } else {
                for (int i = 0; i < tables.length; i++) {
                    if (i == 0) {
                        sql += tables[i] + " JOIN " + tables[i + 1] + " ON " + joinConditions[i] + " ";
                        i++;
                    } else {
                        sql += "JOIN " + tables[i] + " ON " + joinConditions[i - 1] + " ";

                    }
                }
            }

        } else {
            sql += tables[0] + " ";
        }
        if (selection != null) {
            sql += "WHERE " + selection + " ";
        }

        if (groupBy != null) {
            sql += "GROUP BY " + groupBy + " ";
        }

        if (having != null) {
            sql += "HAVING " + having + " ";
        }
        if (orderBy != null) {
            sql += "ORDER BY " + orderBy + " ";
        }

        if (limit != null) {
            sql += "LIMIT " + limit + " ";
        }

        return sql;
    }

    private ResultSet obtenerResultSet(Connection con) throws SQLException {
        String sql = obtenerSQL();
        PreparedStatement stmt = con.prepareStatement(sql);
        if (selectionArgs != null) {
            for (int i = 0; i < selectionArgs.length; i++) {
                stmt.setString((i + 1), selectionArgs[i]);
            }
        }
        ResultSet rs = stmt.executeQuery();
        return rs;
    }

    /**
     * @return the tipoRetorno
     */
    public int getTipoRetorno() {
        return tipoRetorno;
    }

    /**
     * @return the table
     */
    public String[] getTable() {
        return tables;
    }

    /**
     * @return the columns
     */
    public String[] getColumns() {
        return columns;
    }

    /**
     * @return the selection
     */
    public String getSelection() {
        return selection;
    }

    /**
     * @return the selectionArgs
     */
    public String[] getSelectionArgs() {
        return selectionArgs;
    }

    /**
     * @return the groupBy
     */
    public String getGroupBy() {
        return groupBy;
    }

    /**
     * @return the having
     */
    public String getHaving() {
        return having;
    }

    /**
     * @return the orderBy
     */
    public String getOrderBy() {
        return orderBy;
    }

    /**
     * @return the limit
     */
    public String getLimit() {
        return limit;
    }

    /**
     * @return the joinConditions
     */
    public String[] getJoinConditions() {
        return joinConditions;
    }

    /**
     * @return the distinct
     */
    public boolean isDistinct() {
        return distinct;
    }

    /**
     * @param tipoRetorno the tipoRetorno to set
     */
    public void setTipoRetorno(int tipoRetorno) {
        this.tipoRetorno = tipoRetorno;
    }

    /**
     * @param distinct the distinct to set
     */
    public void setDistinct(boolean distinct) {
        this.distinct = distinct;
    }

    /**
     * @param columns the columns to set
     */
    public void setColumns(String[] columns) {
        this.columns = columns;
    }

    /**
     * @param tables the tables to set
     */
    public void setTables(String[] tables) {
        this.tables = tables;
    }

    /**
     * @param joinConditions the joinConditions to set
     */
    public void setJoinConditions(String[] joinConditions) {
        this.joinConditions = joinConditions;
    }

    /**
     * @param selection the selection to set
     */
    public void setSelection(String selection) {
        this.selection = selection;
    }

    /**
     * @param selectionArgs the selectionArgs to set
     */
    public void setSelectionArgs(String[] selectionArgs) {
        this.selectionArgs = selectionArgs;
    }

    /**
     * @param groupBy the groupBy to set
     */
    public void setGroupBy(String groupBy) {
        this.groupBy = groupBy;
    }

    /**
     * @param having the having to set
     */
    public void setHaving(String having) {
        this.having = having;
    }

    /**
     * @param orderBy the orderBy to set
     */
    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }

    /**
     * @param limit the limit to set
     */
    public void setLimit(String limit) {
        this.limit = limit;
    }

    /**
     * @return los modificadores para los JOIN,
     * que pueden ser LEFT, RIGHT, OUTER o cadena vacia ""
     */
    public String[] getJoinModifiers() {
        return joinModifiers;
    }

    /**
     * @param joinModifiers establece los modificadores para los JOIN,
     * que pueden ser LEFT, RIGHT, OUTER o cadena vacia ""
     */
    public void setJoinModifiers(String[] joinModifiers) {
        this.joinModifiers = joinModifiers;
    }
}
